<?php
/**
 * 功能：xls 数据导入导出处理类
 * 说明：
 * 创建日期：2011-05-26
 * 更新日期：
 * 作者：Mirze
 * 补充说明： 
 * 
 */
class parse_excel
{
	private $read_xls;

	public function __construct()
	{
	}

	/**
	 * (功能描述)直接数据导出xls
	 *
	 * @param string filename 文件名
	 * @param array data 数据
	 * @return
	 */
	public function write_xls($filename, $data)
	{
		if(empty($data)) {
			$msg = "数据为空，不做文件导出";
			$this->dialog_title($msg);
		}
		if (file_exists($filename)) {
			$msg = $filename."文件已存在";
			$this->dialog_title($msg);
		}

		header("Content-type:application/vnd.ms-excel");
		header("Content-Disposition:filename={$filename}");

		foreach($data as $k => $v) {
			$numCols = count($v);
			for($i=0; $i<$numCols; $i++) {				
				if($i == $numCols - 1) {
					echo $v[$i]."\t\n";
				} else {
				    echo $v[$i]."\t";
				}
			}
		}
		return $filename;
	}

	/**
	 * (功能描述) 读取csv数据
	 *
	 * @param string filePath 文件路径
	 * @param int length 每条记录长度
	 * @param string delimiter 字段分界符
	 * @return array
	 */
	public function data_csv($filePath = '', $length=1000, $delimiter=',', $encode = "utf-8")
	{
		$result = array();
		$arr_csv = array();
		$ec_phone = ''; //文件名
		$numCols = 0; //字段数
		$row = 1; //初始读取行1

		//setlocale(LC_ALL, 'zh_CN.utf-8'); //解决中文乱码

		$handle = fopen($filePath,"r");
		while ($data = fgetcsv($handle, $length, $delimiter)) {
			$numCols = count($data);
			for ($i=0; $i < $numCols; $i++) {
				//$arr_csv[$row-1][$i] = $data[$i];
				$arr_csv[$row-1][$i] = iconv('GB2312', 'UTF-8', $data[$i]); //解决中文乱码: csv为gb2312编码
			}
			
			$row++; //行
		}
		fclose($handle);

		if(!empty($arr_csv)){
			$path_info = pathinfo($filePath);
			$file_name = $path_info['filename']; //文件名

			$fileName = isset($file_name) ? $file_name : '';
		}

		$result['data'] = $arr_csv;
		$result['fileName'] = $fileName;
		$result['numCols'] = $numCols;

		return $result;
	}

	/**
	 * (功能描述) 读取xls数据
	 *
	 * @param string filePath 文件路径
	 * @param string encode 编码格式
	 * @return array
	 */
	public function data_xls($filePath = '', $encode = "utf-8")
	{
		$result = array();

		if(empty($filePath)) {
			return $result;
		}

		include_once(LIB_BASE_PATH . 'reader_excel.class.php');
		$read_xls = new Spreadsheet_Excel_Reader();

		$path_info = pathinfo($filePath);
		$file_name = $path_info['filename']; //文件名

		$read_xls->setOutputEncoding($encode);
		$read_xls->read($filePath);
		//error_reporting(E_ALL ^ E_NOTICE);
		if($read_xls->sheets) {
			$result = $read_xls->sheets;
			$result['file_name'] = isset($file_name) ? $file_name : '';
		}

		return $result;
	}

	/**
	 * (功能描述)
	 *
	 * @param array data 读取数据
	 * @param int is_head 数据是否有表头信息: 0 无 1 有
	 * @return
	 */
	public function data_parse_csv($data_ex, $is_head = 1)
	{
		$result = array();

		$data = isset($data_ex['data']) ? $data_ex['data']: array(); //读取数据
		$ec_phone = isset($data_ex['ec_phone']) ? $data_ex['ec_phone']: ''; //券商手机号

		if(empty($data)) {
			$msg = "内容为空";
			$this->dialog_title($msg);
		}
		if(empty($ec_phone)) {
			$msg = "券商手机号获取失败";
			$this->dialog_title($msg);
		}

		$item_ru = Factory::getItemClass("relation_user");

		//1. 券商手机号检测
		$jid = $item_ru->exist_broker($ec_phone);
		if(0 == $jid) {
			$msg = "券商不存在";
			$this->dialog_title($msg);
		}

		foreach($data as $k => $v) {
			if(0 == $k && $is_head) {				
				continue;
			}
			$phone = trim($v[0]); //手机号
			$y_phone = trim($v[2]); //营业部私密账户手机号
			$t_phone = trim($v[3]); //投顾私密账户手机号

			$name = trim($v[1]); //用户名
			$sign_type = trim($v[4]); //签约关系

			//获取uid			
			$uid = $item_ru->get_yuncai_uid($phone);
			if(empty($uid)) {
				$msg = "运财用户ID注册失败";
				$this->dialog_title($msg);
				break;
			}

			//2. 营业部手机检测: 139->yid->in_array(jid=>yid)			
			$yid = $item_ru->exist_hall($y_phone, $jid);
			if(0 == $yid) {
				$msg = "营业部不存在";
				$this->dialog_title($msg);
				break;
			}

			//3. 营业部手机检测: 139->tid->in_array(jid=>tid)			
			$tid = $item_ru->exist_invest($t_phone, $jid);
			if(0 == $tid) {
				$msg = "投顾不存在";
				$this->dialog_title($msg);
				break;
			}

			//echo $jid.">>".$yid.">>".$tid."<BR />";

			//4. 确认券商，营业部，投顾关系
			$relation = $item_ru->broker_hall_invest($jid, $yid, $tid);			
			if(empty($relation)) {
				$msg = "关系错误";
				$this->dialog_title($msg);
				break;
			}

			//输出（运财uid，手机号，用户名，券商uid，营业部uid，投顾uid，签约类型
			$result[$k][0] = $uid;
			$result[$k][1] = $phone;
			$result[$k][2] = $name;
			$result[$k][3] = $jid;
			$result[$k][4] = $yid;
			$result[$k][5] = $tid;
			$result[$k][6] = $sign_type;
		}

		return $result;
	}

	/**
	 * (功能描述) 分析：批量用户导入数据
	 * xls数据格式: //手机号，用户名，营业部私密账户手机号，投顾私密账户手机号，签约类型
	 * @param array data_xls 导入xls数据
	 * @param int is_head 数据是否有表头信息: 0 无 1 有
	 * @return
	 */
	public function data_parse_xls($data_xls, $is_head = 1, $is_write = 0, $filename='out.xls')
	{
		//初始化
		$result = array(
			'numRows' => 0, //数据条数
			'numCols' => 0,  //每条数据列数
			'state' => 0, //0 空数据 1 正常 2 数据异常
			'msg' => '获取数据为空',  //提示信息
			'list' => array(), //数据列表
		);

		//记录条数
		$num = isset($data_xls[0]['numRows']) ? intval($data_xls[0]['numRows']) : 0;
		$result['numRows'] = ($is_head == 1) ? ($num-1) : $num; //数据记录条数: 去表头
		if(empty($result['numRows'])){
			return $result;
		}		
		//字段数:每条分类数
		$numCols = isset($data_xls[0]['numCols']) ? intval($data_xls[0]['numCols']) : 0;
		$file_name = $data_xls['file_name']; //文件名
		$arr_data = empty($data_xls[0]['cells']) ? array() : $data_xls[0]['cells']; //数据
		if(empty($arr_data)){
			return $result;
		}
		$result['numCols'] = count($arr_data[1]);//实际列数
		
		$arr_xls = array();
		for ($i = 1; $i <= $num; $i++) {
			if(1 == $i && $is_head) {				
				continue;
			}
			if(!empty($arr_data[$i][1])) {
				for($j = 0; $j < $result['numCols']; $j++) {
					$arr_xls[$i-1][$j] = trim($arr_data[$i][$j+1]);
				}
			}
		}
		$result['state'] = 1;
		$result['msg'] = "数据读取成功";
		$result['list'] = $arr_xls;

		//导出xls
		if($is_write) {
			$res = $this->write_xls($filename, $arr_xls);
			exit;
		}

		return $result;		
	}

	/**
	 * (功能描述) 批量更新用户资料
	 * 批量用户开通
	 * @param array data_xls 导入xls数据
	 * @return
	 */
	public function data_reg($data_xls, $is_hand=1)
	{
		$result = array();
		
		//记录条数
		$numRows = isset($data_xls[0]['numRows']) ? intval($data_xls[0]['numRows']) : 0;
		if(empty($numRows)) {
			return $result;
		}
		
		//字段数:每条分类数
		$numCols = isset($data_xls[0]['numCols']) ? intval($data_xls[0]['numCols']) : 0;
		
		//项目类
		$item_yu = Factory::getItemClass("yuncai_user_item");
		$item_se = Factory::getItemClass("securities_item");
		$item_sa = Factory::getItemClass("sales_hall");
		$item_ad = Factory::getItemClass("advisor_item");
		$item_au = Factory::getItemClass("advisor_user_item");
		//$redis = Factory::getItemClass("mem_redis");
		
		$j = 0;
		$arr_xls = array();
		$k = 0;
		$arr_err = array();
		$s = (1 == $is_hand) ? 2 : 1;
		
		for($i=$s; $i<=$numRows; $i++) {
			
			$yuncai_uid = isset($data_xls[0]['cells'][$i][1]) ? intval($data_xls[0]['cells'][$i][1]) : 0;
			$uid_139 = isset($data_xls[0]['cells'][$i][2]) ? intval($data_xls[0]['cells'][$i][2]) : 0;
			$securities_uid = isset($data_xls[0]['cells'][$i][3]) ? intval($data_xls[0]['cells'][$i][3]) : 0;
			$sales_uid = isset($data_xls[0]['cells'][$i][4]) ? intval($data_xls[0]['cells'][$i][4]) : 0;
			$advisor_uid = isset($data_xls[0]['cells'][$i][5]) ? intval($data_xls[0]['cells'][$i][5]) : 0;
			$type = isset($data_xls[0]['cells'][$i][6]) ? intval($data_xls[0]['cells'][$i][6]) : 0;
			
			//格式检测（运财uid，139uid，券商uid，营业部uid，投顾uid，签约类型）
			$is_format = false;
			if($yuncai_uid != 0 && $uid_139 != 0 && $type != 0) {
				if(1 == $type) {
					if($advisor_uid != 0) {
						$is_format = true;
					}
				} elseif(2 == $type) {
					if($sales_uid != 0 && $advisor_uid != 0) {
						$is_format = true;
					}
				} elseif(3 == $type) {
					if($securities_uid != 0 && $sales_uid != 0 && $advisor_uid != 0) {
						$is_format = true;
					}
				}
			}
			
			//检测（券商uid，营业部uid，投顾uid，运财uid）是否存在
			$is_exits = false;
			$isSecuritiesUid = $item_se->isSecuritiesUid($securities_uid);
			$isSalesUid = $item_sa->isSalesUid($sales_uid);
			$isAdvisorUid = $item_ad->isAdvisorUid($advisor_uid);
			$isYuncaiUid = $item_yu->isYuncaiUid($yuncai_uid);
			if($isYuncaiUid) {
				if(1 == $type) {
					if($isAdvisorUid) {
						$is_exits = true;
					}
				} elseif(2 == $type) {
					if($isSalesUid && $isAdvisorUid) {
						$is_exits = true;
					}
				} elseif(3 == $type) {
					if($isSecuritiesUid && $isSalesUid && $isAdvisorUid) {
						$is_exits = true;
					}
				}
			}
			printf("%d && %d<br/>",$is_format,$is_exits);
			
			//存入数组
			if($is_format && $is_exits) {
				$arr_xls[$j]['yuncai_uid'] = $yuncai_uid;
				$arr_xls[$j]['uid_139'] = $uid_139;
				$arr_xls[$j]['securities_uid'] = $securities_uid;
				$arr_xls[$j]['sales_uid'] = $sales_uid;
				$arr_xls[$j]['advisor_uid'] = $advisor_uid;
				$arr_xls[$j]['type'] = $type;
				$j++;
			} else {
				$arr_err[$k]['yuncai_uid'] = $yuncai_uid;
				$arr_err[$k]['uid_139'] = $uid_139;
				$arr_err[$k]['securities_uid'] = $securities_uid;
				$arr_err[$k]['sales_uid'] = $sales_uid;
				$arr_err[$k]['advisor_uid'] = $advisor_uid;
				$arr_err[$k]['type'] = $type;
				$k++;
			}
			
		}
		
		if(!empty($arr_xls)) {
			foreach($arr_xls as $temp_val) {
				//根据139uid更新用户资料(yuncai_user表)
				if($item_yu->update_user($temp_val)) {
					//根据签约类型  “签约关系” 中建立关系 添加私密收听关系，组id为0
					$item_au->updateAdvisorUser($temp_val);
				}
			}
			//设置投顾与用户缓存
			//$redis->del_advisor_user($arr_xls);
			//$redis->set_advisor_user($arr_xls);
			//var_dump($redis->get_advisor_user($arr_xls));
		}
		
		return array($arr_xls,$arr_err);
		
	}
	
	/**
	 * (功能描述) 分析企业用户xls数据，批量开通
	 * xls数据格式：运财注册手机号，139私密用户手机号，私密昵称，私密密码，139公开用户手机号，公开昵称，公开密码
	 * @param array data_xls 导入xls数据
	 * @return
	 */
	public function data_co($data_xls, $is_head = 1, $is_write = 0, $filename='')
	{
		$result = array();
		
		//记录条数
		$num = isset($data_xls[0]['numRows']) ? intval($data_xls[0]['numRows']) : 0;
		if(empty($num)){
			return $result;
		}
		
		//字段数:每条分类数
		$numCols = isset($data_xls[0]['numCols']) ? intval($data_xls[0]['numCols']) : 0;
		
		$arr_xls = array();
		$j = 0;
		for ($i = 1; $i <= $num; $i++) {
			//xls含表头信息跳过
			if(1 == $i && $is_head) {
				continue;
			}

			$phone = trim($data_xls[0]['cells'][$i][1]);			
			$prv_phone = trim($data_xls[0]['cells'][$i][2]);
			$prv_name = trim($data_xls[0]['cells'][$i][3]);
			$prv_pwd = trim($data_xls[0]['cells'][$i][4]);
			$open_phone = trim($data_xls[0]['cells'][$i][5]);
			$open_name = trim($data_xls[0]['cells'][$i][6]);
			$open_pwd = trim($data_xls[0]['cells'][$i][7]);

			//检验手机号是否运财注册
			$uid = $this->api_user_reg($phone);
			if(0 == $uid) {
				$msg = $phone . "手机号注册失败";
				$this->dialog_title($msg);
				break;
			}
			if(empty($prv_phone) && empty($open_phone)) {
				$msg = $phone . "手机号私密，公开手机号不合法或为空";
				$this->dialog_title($msg);
				break;
			}

			//输出（运财uid，手机号，昵称，密码，类型（0，私密，1公开)）
			//私密				
			if(0 < $prv_phone) {
				$arr_xls[$j][0] = $uid;
				$arr_xls[$j][1] = $prv_phone;
				$arr_xls[$j][2] = $prv_name;
				$arr_xls[$j][3] = $prv_pwd;
				$arr_xls[$j][4] = 0;

				$j++;
			}

			//公开				
			if(0 < $open_phone) {
				$arr_xls[$j][0] = $uid;
				$arr_xls[$j][1] = $open_phone;
				$arr_xls[$j][2] = $open_name;
				$arr_xls[$j][3] = $open_pwd;
				$arr_xls[$j][4] = 1;

				$j++;
			}
		}

		//导出xls
		if($is_write) {
			$res = $this->write_xls($filename, $arr_xls);
			exit;
		}

		$result = $arr_xls;
		
		return $result;
	}

	//获取运财uid,或开通注册
	public function api_user_reg($phone)
	{
		//1. 获取uid
		//2. 未获取uid, 注册开通uid
		//否则返回0
		$uid = $phone;
		return $uid;		
	}

	//提示语弹窗：临时用
	public function dialog_title($msg='', $isMsg=0)
	{
		switch($isMsg) {
			case '0': //msg 为 错误代码
				alertMsg($msg, 'index.php?c=import_batch');
			    break;
			default:
				alertMsg2($msg, 'index.php?c=import_batch');			
			    break;
		}		
		exit;		
	}

	function exceltimtetophp($days,$time=false)
	{
		if(is_numeric($days))
		{
			$jd = GregorianToJD(1, 1, 1970);
			$gregorian = JDToGregorian($jd+intval($days)-25569);
			$myDate = explode('/',$gregorian);
			$myDateStr= str_pad($myDate[2],4,'0',  STR_PAD_LEFT)."-".str_pad($myDate[0],2,'0',STR_PAD_LEFT)."-".str_pad($myDate[1],2,'0',   STR_PAD_LEFT).($time?"00:00:00":'');

			return $myDateStr;
		}
		return $time;
	}



}
?>
